package org.langqiao.experiment.experiment10;
import java.awt.BorderLayout;
import java.awt.FlowLayout;
import java.awt.GridLayout;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.IOException;
import java.sql.*;


import javax.swing.*;
import javax.swing.table.DefaultTableModel;

class StuInfor extends JFrame implements ActionListener{
	public static final String URL="jdbc:mysql://localhost:3308/stuInfor?user=root&password=123456";
	private int m,flag=0;
	private JMenuBar mb;
	private JMenu m1,m2;
	private JMenuItem mi[];
	private Connection conn;
	private JTable Infortable; 
	private String table,labelName1,labelName2,comboBoxName1,comboBoxName2;
	private JButton select,all,delete,add;
	private String tableDatas[][],tableColTitles[];
	private DefaultTableModel model;
	private JComboBox jcb1,jcb2,jcb3,jcb4;
	
	public  StuInfor(String title){
		super(title);
		m=0;
		this.setBounds(m+200,m+100,535,350);
		this.setDefaultCloseOperation(HIDE_ON_CLOSE);
		this.setLayout(new BorderLayout());
		
		mb=new JMenuBar();
		this.setJMenuBar(mb);
		m1=new JMenu("信息管理");
		m2=new JMenu("帮助");
		mb.add(m1);
		mb.add(m2);
		String menuitemName[]={"学生信息","课程信息","学生成绩信息","关于系统","帮助文档"};
		mi=new JMenuItem[menuitemName.length];
		for(int i=0;i<menuitemName.length;i++){
			mi[i]=new JMenuItem(menuitemName[i]);
			if(i<3)  m1.add(mi[i]);
			else     m2.add(mi[i]);
			mi[i].addActionListener(this);
		}
		
		String sql=change(title,flag);
		try {
			JPanel p=qureyBar(labelName1,labelName2,table);
			this.add(p,"North");
		} catch (Exception e) {
			e.printStackTrace();
		}
		sql=change(title,flag);
System.out.println(sql+1);		
		try {
			query(sql);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		//delete=new JButton("删除");
		model=new DefaultTableModel(tableDatas,tableColTitles);
		Infortable=new JTable(model);
		//Infortable.getSelectedRow();
		this.add(new JScrollPane(new JPanel().add(Infortable)));
		
		JPanel pp=new JPanel(new FlowLayout());
		delete=new JButton("删除");
		add=new JButton("添加");
		pp.add(delete);
		pp.add(add);
		delete.addActionListener(this);
		add.addActionListener(this);
		this.getContentPane().add(pp,"East");
		//this.setResizable(false);
		this.setVisible(true);
		
	}
	
	public JPanel qureyBar(String str1,String str2,String table)throws Exception{
		String sql1="select "+str1+" from "+table;
		String sql2="select distinct "+str2+" from "+table;
		
		conn=DriverManager.getConnection(URL);
		//String sql="select * from "+table;
		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery(sql1);
		int rows=0;
		while(rs.next())
			rows++;
		rs.beforeFirst();
		String jcbText1[]=new String[rows];
		int i=0;
		while(rs.next()){
			jcbText1[i]=rs.getString(1);
			i++;
		}
		
		rs=stmt.executeQuery(sql2);
		String jcbText2[]=new String[rows];
		i=0;
		while(rs.next()){
			jcbText2[i]=rs.getString(1);
			i++;
		}
		JLabel l1=new JLabel(str1+":");
		JLabel l2=new JLabel(str2+":");
		jcb1=new JComboBox(jcbText1);
		jcb2=new JComboBox(jcbText2);
		
		jcb1.setEditable(true);
		jcb2.setEditable(true);
		
		jcb1.setSelectedItem("");
		jcb2.setSelectedItem("");
		//comboBoxName1=(String) jcb1.getSelectedItem();
		//comboBoxName2=(String) jcb2.getSelectedItem();
		
		/**/select=new JButton("查找");
		all=new JButton("显示全部");
		select.addActionListener(this);
		all.addActionListener(this);
		
		JPanel p=new JPanel();
		p.setLayout(new FlowLayout());
		p.add(l1);
		p.add(jcb1);
		p.add(l2);
		p.add(jcb2);
		p.add(select);
		p.add(all);
		stmt.close();
		conn.close();
		return p;
	}
	
	public String change(String str,int flag){
		String str1="select *from ";
		if(str.equals("学生信息")) {
			table="student";
			str1+="student";
			this.labelName1="sname";
			this.labelName2="sdepart";
		}
		if(str.equals("课程信息")) {
			table="course";
			str1+="course";
			this.labelName1="cno";
			this.labelName2="cname";
		}
		if(str.equals("学生成绩信息")) {
			table="SC";
			str1+="SC";
			this.labelName1="sname";
			this.labelName2="cname";
		}//str1+=" where "+labelName1+"="+comboBoxName1;
		//str1="select *from student where sname='李明'";
		;
		if(flag==1){
//System.out.println (labelName1);
			if(comboBoxName1!=null&&!comboBoxName1.trim().equals(""))
				str1+=" where "+labelName1+"='"+comboBoxName1+"'";
			else if(comboBoxName2!=null&&!comboBoxName2.trim().equals(""))
				str1+=" where "+labelName2+"='"+comboBoxName2+"'";
			else
				JOptionPane.showMessageDialog(this, "查询条件不能为空！");
		}
		return str1;
	}
	
	public void actionPerformed(ActionEvent e) {
		String command=e.getActionCommand();
		if(command.equals("学生信息")&&!this.getTitle().equals("学生信息")){
			new StuInfor("学生信息");
			//init("学生信息");
			m+=20;
			
		}
		if(command.equals("课程信息")&&!this.getTitle().equals("课程信息")){
			new StuInfor("课程信息");
			//init("课程信息");
			m+=20;
			
		}
		if(command.equals("学生成绩信息")&&!this.getTitle().equals("学生成绩信息")){
			new StuInfor("学生成绩信息");
			m+=20;
			//init("学生成绩信息");
			
		}
		if(command.equals("关于系统")){
			JOptionPane.showMessageDialog(this,"版本1.0 作者：ww");
		}
		if(command.equals("帮助文档")){
			Runtime rt=Runtime.getRuntime();
			try {
				rt.exec("notepad.exe help.txt");
			} catch (IOException e1) {
				e1.printStackTrace();
			}
		}
		if(e.getSource()==select){
			flag=1;
			comboBoxName1=(String) jcb1.getSelectedItem();
			comboBoxName2=(String) jcb2.getSelectedItem();
			String sql=this.change(this.getTitle(), flag);
System.out.println(sql);
			try {
				this.query(sql);
				this.model=new DefaultTableModel(this.tableDatas,this.tableColTitles);
				Infortable.setModel(model);
				//Infortable.removeAll();
				//Infortable.addColumn(aColumn)
				//init("学生信息");
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
			
		}
		if(e.getSource()==all){
			flag=0;
			String sql=this.change(this.getTitle(), flag);
			System.out.println(sql);
			try {
				this.query(sql);
				this.model=new DefaultTableModel(this.tableDatas,this.tableColTitles);
				Infortable.setModel(model);
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		}
		if(e.getActionCommand().equals("添加")){
			new AddDialog(this,this.getTitle(),true);
			JOptionPane.showMessageDialog(this, "添加成功！");
		}
		if(e.getActionCommand().equals("删除")){
			int n[]=Infortable.getSelectedRows();
			if(n.length<=0){
				JOptionPane.showMessageDialog(this, "你没有选中要删除的行号！");
				return;
			}
			else{
				//Infortable.remove(Infortable.getSelectedRow());
				String str1,sql="";
				if(this.getTitle().equals("学生信息")){
					//for(int i=0;i<n.length;i++)
					str1=model.getValueAt(n[0],0).toString();
					sql="delete from student where sno='"+str1+"'";
System.out.println(sql);
				}
				if(this.getTitle().equals("课程信息")){
					//for(int i=0;i<n.length;i++)
					str1=model.getValueAt(n[0],0).toString();
					sql="delete from course where cno='"+str1+"'";
				}
				if(this.getTitle().equals("学生成绩信息")){
					//for(int i=0;i<n.length;i++)
					str1=model.getValueAt(n[0],0).toString();
					String str2=model.getValueAt(n[0],2).toString();
					sql="delete from score where sno='"+str1+"' and cno='"+str2+"'";
				}
				try {
System.out.println(sql);
					conn=DriverManager.getConnection(URL);
					Statement stmt=conn.createStatement();
					stmt.executeUpdate(sql);
System.out.println(sql);
					stmt.close();
					conn.close();
				} catch (SQLException e1) {
					JOptionPane.showMessageDialog(this, "删除错误！");
					e1.printStackTrace();
				}
				model.removeRow(Infortable.getSelectedRow());
				JOptionPane.showMessageDialog(this, "删除成功！");
			}
		}
			
	}
	
	
	
	public void query(String sql)throws SQLException{
		conn=DriverManager.getConnection(URL);
		//String sql="select * from "+table;
		Statement stmt=conn.createStatement();
		ResultSet rs=stmt.executeQuery(sql);
		ResultSetMetaData rsmd=rs.getMetaData();
		int cols=rsmd.getColumnCount();
		//String titlesName[]=new String[cols];
		this.tableColTitles=new String[cols];
		//this.tableColTitles=new String[cols+1];
		//tableColTitles[0]="操作";
		for(int i=1;i<=cols;i++){
			tableColTitles[i-1]=rsmd.getColumnName(i);
			//titlesName[i-1]=rsmd.getColumnName(i);
		}
		int rows=0;
		while(rs.next())
			rows++;
		rs.beforeFirst();
		//String results[][]=new String[rows+1][cols];
		tableDatas=new String[rows][cols];
		//for(int i=0;i<rows;i++)
			//tableDatas[i][0]="DEL";
			//results[0][i]=titlesName[i];
		//由于不显示标题 故增加标题列
		
		for(int i=0;rs.next();i++)
			for(int j=1;j<cols+1;j++)
				tableDatas[i][j-1]=rs.getString(j);
		
		rs.close();
		stmt.close();
		conn.close();
		//return new JTable(results,titlesName);//可能是JDK版本问题本机不会显示出表的标题
	}
	
	public static void main(String[]args)throws Exception{
		Class.forName("com.mysql.jdbc.Driver");
		new StuInfor("学生信息");
		//StuInfor f=new StuInfor("学生信息");
		//f.init("学生信息");
	}

}